﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Utils;
using MySql.Data.MySqlClient;

namespace Data
{
    public class Planes
    {
        private Carreras _hnd_carreras = new Carreras();

        private Carreras HndCarreras
        {
            get { return _hnd_carreras; }
        }

        private Entities.Plan CreateInstance(DataRow row)
        {
            return new Entities.Plan(
                row["id"].ToInt32(),
                row["id_carrera"].ToInt32(),
                row["nombre"].ToString()
            );
        }

        private Entities.Collections.PlanesCollection CreateCollection(DataTable table)
        {
            Entities.Collections.PlanesCollection c = new Entities.Collections.PlanesCollection();
            foreach (DataRow r in table.Rows) c.Add(CreateInstance(r));

            return c;
        }

        public Entities.Plan GetByID(int id)
        {
            string s = "SELECT id, id_carrera, nombre FROM planes WHERE id = @id";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@id", id));
            Query q = new Query(s, l);
            DataTable t = q.Execute();

            if (t.Rows.Count > 0) return CreateInstance(t.Rows[0]);
            else return null;
        }

        public Entities.Collections.PlanesCollection GetByCarrera(Entities.Carrera carrera)
        {
            string s = "SELECT id, id_carrera, nombre FROM planes WHERE id_carrera = @id_carrera ORDER BY nombre";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@id_carrera", carrera.ID));

            return CreateCollection(new Query(s, l).Execute());
        }

        public Entities.Collections.PlanesCollection GetCollection()
        {
            string s = "SELECT id, id_carrera, nombre FROM planes ORDER BY nombre";

            return CreateCollection(new Query(s, new List<MySqlParameter>()).Execute());
        }

        private void Insert(Entities.Plan instance)
        {
            if (instance.State != Commons.EntityState.New) return;

            string s = "INSERT INTO planes ( id_carrera, nombre ) VALUES ( @id_carrera, @nombre )";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@id_carrera", HndCarreras.GetByID(instance.IDCarrera).ID));
            l.Add(new MySqlParameter("@nombre", instance.Nombre));
            new NonQuery(s, l).Execute();
        }

        private void Update(Entities.Plan instance)
        {
            if (instance.State != Commons.EntityState.Modified) return;

            string s = "UPDATE planes SET id_carrera = @id_carrera, nombre = @nombre WHERE id = @id";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@id_carrera", HndCarreras.GetByID(instance.IDCarrera).ID));
            l.Add(new MySqlParameter("@nombre", instance.Nombre));
            l.Add(new MySqlParameter("@id", instance.ID));
            new NonQuery(s, l).Execute();
        }

        public void Delete(Entities.Plan instance)
        {
            if (instance.State != Commons.EntityState.Deleted) return;

            string s = "DELETE FROM planes WHERE id = @id";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@id", instance.ID));
            new NonQuery(s, l).Execute();

            OnDelete(instance);
        }

        public void OnDelete(Entities.Plan instance)
        {
            if (instance.State != Commons.EntityState.Deleted) return;

            // comisiones
            Comisiones comisiones = new Comisiones();
            comisiones.GetByPlan(instance).ForEach(i =>
            {
                i.State = Commons.EntityState.Deleted;
                comisiones.Delete(i);
            });

            // materias
            Materias materias = new Materias();
            materias.GetByPlan(instance).ForEach(i =>
            {
                i.State = Commons.EntityState.Deleted;
                materias.Delete(i);
            });

            // alumnos
            Alumnos alumnos = new Alumnos();
            alumnos.GetByPlan(instance).ForEach(i =>
            {
                i.State = Commons.EntityState.Deleted;
                alumnos.Delete(i);
            });
        }

        public void Save(Entities.Plan instance)
        {
            if (instance.State == Commons.EntityState.New) Insert(instance);
            else Update(instance);
        }
    }
}
